﻿CREATE PROCEDURE [dbo].[yaf_mail_createwatch](
                @TopicID INT,
                @From    NVARCHAR(50),
                @Subject NVARCHAR(100),
                @Body    NTEXT,
                @UserID  INT)
AS
    BEGIN
        INSERT INTO yaf_Mail
                   (FromUser,
                    ToUser,
                    Created,
                    Subject,
                    Body)
        SELECT @From,
               b.Email,
               Getdate(),
               @Subject,
               @Body
        FROM   yaf_WatchTopic a,
               yaf_User b
        WHERE  b.UserID <> @UserID
        AND b.UserID = a.UserID
        AND a.TopicID = @TopicID
        AND (a.LastMail IS NULL 
              OR a.LastMail < b.LastVisit)
        INSERT INTO yaf_Mail
                   (FromUser,
                    ToUser,
                    Created,
                    Subject,
                    Body)
        SELECT @From,
               b.Email,
               Getdate(),
               @Subject,
               @Body
        FROM   yaf_WatchForum a,
               yaf_User b,
               yaf_Topic c
        WHERE  b.UserID <> @UserID
        AND b.UserID = a.UserID
        AND c.TopicID = @TopicID
        AND c.ForumID = a.ForumID
        AND (a.LastMail IS NULL 
              OR a.LastMail < b.LastVisit)
        AND NOT EXISTS (SELECT 1
                        FROM   yaf_WatchTopic x
                        WHERE  x.UserID = b.UserID
                        AND x.TopicID = c.TopicID)
        UPDATE yaf_WatchTopic
        SET    LastMail = Getdate()
        WHERE  TopicID = @TopicID
        AND UserID <> @UserID
        UPDATE yaf_WatchForum
        SET    LastMail = Getdate()
        WHERE  ForumID = (SELECT ForumID
                   FROM   yaf_Topic
                   WHERE  TopicID = @TopicID)
        AND UserID <> @UserID
    END


